package com.cjx.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
public class GetConnection {
	private static String dvrive = "oracle.jdbc.driver.OracleDriver";
	private static String path = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
	private static String name = "cjx";
	private static String pass = "cjx";
	Connection conn;
	Statement stmt;
	ResultSet rs=null;
	public Connection getConn() {
		try {
			Class.forName(dvrive);
			conn = DriverManager.getConnection(path, name, pass);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
	public void closeConn(){
		try {
			if(rs!=null)
			rs.close();
			if(stmt!=null)
			stmt.close();
			if(conn!=null)
			conn.close();
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
	}

	/**
	 * <br>
	 * 方法说明：执行查询SQL语句 <br>
	 * 输入参数：Connection con 数据库连接 <br>
	 * 输入参数：String sql 要执行的SQL语句 <br>
	 * 返回类型：void
	 */
	public List<HashMap> query(Connection con, String sql) {
		List list = new ArrayList();
			try {
				if (con == null) {
					throw new Exception("database connection can't use!");
				}
				if (sql == null)
					throw new Exception(
							"check your parameter: 'sql'! don't input null!");
				
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			// 第五步：处理结果集
			ResultSetMetaData rmeta = rs.getMetaData();
			// 获得数据字段个数
			int numColumns = rmeta.getColumnCount();
			HashMap map=null;
			while (rs.next()) {
				 map= new HashMap();
				for (int i = 1; i <= numColumns; i++) {
					//System.out.println(rmeta.getColumnName(i)+rs.getObject(i));
					map.put(rmeta.getColumnName(i), rs.getObject(i));
				}
				list.add(map);
			}
		} catch (Exception e) {
			System.out.println("query error:" + e);
		}finally{
			closeConn();
		}
		return list;
	}

	/**
	 * <br>
	 * 方法说明：执行插入、更新、删除等没有返回结果集的SQL语句 <br>
	 * 输入参数：Connection con 数据库连接 <br>
	 * 输入参数：String sql 要执行的SQL语句 <br>
	 * 返回类型：void
	 */
	public int execute(Connection con, String sql) {
		int num = 0;
		try {
			if (con == null)
				return num;
			// 第三步：获取Statement对象
			Statement stmt = con.createStatement();
			// 第四步：执行数据库操作（更新操作）
			num = stmt.executeUpdate(sql);
			System.out.println("update executed successly");
		} catch (Exception e) {
			System.out.println("execute error: sql = " + sql);
			System.out.println(e);
		}// end try catch
		return num;
	}// end execute
}
